package com.weic.spark.scala.p1

import org.apache.spark.sql.SparkSession

/**
 * @Auther:BigData-weic
 * @ClassName:_01SparkSqlTest
 * @Date:2020/12/9 19:37
 * @功能描述: $FunctionDescription
 * @Version:1.0
 */
object _01SparkSqlTest {
	def main(args: Array[String]): Unit = {
		val spark = SparkSession.builder()
    		.appName("_01SparkSqlTest")
    		.master("local[*]")
    		.getOrCreate()
		val pdf = spark.read.json("file:\\F:\\datas\\spark\\sql\\people.json")
		println("--获取元数据--")
		pdf.printSchema()
		println("-----获取表中数据--")
		pdf.show()
		println("-----huoqu个别字段--")
		pdf.select("name","province").show()
		println("条件查询----》条件查询一定要隐式转化---")
		import spark.implicits._
		pdf.select($"name",($"age"+1).as("年龄"),$"province")
				.where("province = \"广东\"")
    			.show()
		println("分组统计")
		pdf.select("province").groupBy("province")
    			.count()
    			.show()
		println("复杂统计-------L>")
		pdf.groupBy("province")
    			.agg(Map(
					"age" -> "max",
					"province" -> "count"
				)).show()
		//在内存中声明一张表
		pdf.createOrReplaceTempView("people")
		//sql查询
		val sql =
			"""
			  |select
			  |   province,
			  |   count(1) counts,
			  |   max(age) mxAge
			  |from people
			  |group by province
			  |""".stripMargin
		spark.sql(sql).show()

		spark.stop()
	}

}
